package com.edu.dao.impl;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.edu.dao.BaseDao;
import com.edu.dao.galleryDao;
import com.edu.entity.Founder;
import com.edu.entity.Information;
import com.edu.entity.Reason;
import com.edu.entity.Tu;
import com.edu.entity.User;
import com.edu.entity.gallery;


public class galleryDaoImpl implements galleryDao {

	

	@Override
	public List<gallery> allGallery(String gallery) {
		// TODO Auto-generated method stub
		List<gallery> galleryList = new ArrayList<gallery>();
		try {
			Connection conn = BaseDao.getConn();
			Statement ps = conn.createStatement();
			String sql = "SELECT * FROM gallery  ORDER BY id DESC LIMIT 0, 6";
			System.out.println(sql);
			ResultSet rs = ps.executeQuery(sql);
			while (rs.next()) {
				gallery ngallery = new gallery();
				ngallery.setId(rs.getInt("id"));
				ngallery.setImgSrc(rs.getString("img"));
				galleryList.add(ngallery);
			}
			BaseDao.closeAll(conn, ps, rs);
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		return galleryList;
	}

	@Override
	public List<Information> allInformation() {
		// TODO Auto-generated method stub
		List<Information> galleryList = new ArrayList<Information>();
		try {
			Connection conn = BaseDao.getConn();
			Statement ps = conn.createStatement();
			String sql = "SELECT COUNT(1) AS count FROM user WHERE userIdentity LIKE '%%志愿者'";
			System.out.println(sql);
			ResultSet rs = ps.executeQuery(sql);
			while (rs.next()) {
				Information ngallery = new Information();
				ngallery.setGreenUser(rs.getInt("count"));
				galleryList.add(ngallery);
			}
			BaseDao.closeAll(conn, ps, rs);
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		return galleryList;
	}

	@Override
	public List<Information> allInformationYou() {
		// TODO Auto-generated method stub
		List<Information> galleryList = new ArrayList<Information>();
		try {
			Connection conn = BaseDao.getConn();
			Statement ps = conn.createStatement();
			String sql = "SELECT COUNT(1) AS count1 FROM user WHERE userIdentity = '优秀志愿者'";
			System.out.println(sql);
			ResultSet rs = ps.executeQuery(sql);
			while (rs.next()) {
				Information ngallery = new Information();
				ngallery.setOutstandingUser(rs.getInt("count1"));
				galleryList.add(ngallery);
			}
			BaseDao.closeAll(conn, ps, rs);
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		return galleryList;
	}

	@Override
	public List<Information> allInformationMoney() {
		// TODO Auto-generated method stub
		List<Information> galleryList = new ArrayList<Information>();
		try {
			Connection conn = BaseDao.getConn();
			Statement ps = conn.createStatement();
			String sql = "SELECT SUM(donationMoney) AS sum1 FROM contribution";
			System.out.println(sql);
			ResultSet rs = ps.executeQuery(sql);
			while (rs.next()) {
				Information ngallery = new Information();
				ngallery.setMoeny(rs.getInt("sum1"));
				galleryList.add(ngallery);
			}
			BaseDao.closeAll(conn, ps, rs);
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		return galleryList;
	}

	@Override
	public List<Information> allInformationMoneyUser() {
		// TODO Auto-generated method stub
		List<Information> galleryList = new ArrayList<Information>();
		try {
			Connection conn = BaseDao.getConn();
			Statement ps = conn.createStatement();
			String sql = "SELECT COUNT(DISTINCT donationName) AS count2 FROM contribution";
			System.out.println(sql);
			ResultSet rs = ps.executeQuery(sql);
			while (rs.next()) {
				Information ngallery = new Information();
				ngallery.setMoenyUser(rs.getInt("count2"));
				galleryList.add(ngallery);
			}
			BaseDao.closeAll(conn, ps, rs);
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		return galleryList;
	}

	/**
	 *
	 */
	@Override
	public List<Information> allInformationXian() {
		// TODO Auto-generated method stub
		List<Information> galleryList = new ArrayList<Information>();
		try {
			Connection conn = BaseDao.getConn();
			Statement ps = conn.createStatement();
			String sql = "SELECT  COUNT(theme) AS sum2 FROM donations";
			System.out.println(sql);
			ResultSet rs = ps.executeQuery(sql);
			while (rs.next()) {
				Information ngallery = new Information();
				ngallery.setProject(rs.getInt("sum2"));
				galleryList.add(ngallery);
			}
			BaseDao.closeAll(conn, ps, rs);
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		return galleryList;
	}

	@Override
	public List<Information> allInformationHuo() {
		// TODO Auto-generated method stub
		List<Information> galleryList = new ArrayList<Information>();
		try {
			Connection conn = BaseDao.getConn();
			Statement ps = conn.createStatement();
			String sql = "SELECT COUNT(title) AS sum3 FROM newNvent";
			System.out.println(sql);
			ResultSet rs = ps.executeQuery(sql);
			while (rs.next()) {
				Information ngallery = new Information();
				ngallery.setActivity(rs.getInt("sum3"));
				galleryList.add(ngallery);
			}
			BaseDao.closeAll(conn, ps, rs);
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		return galleryList;
	}

	@Override
	public List<Founder> Founders() {
		// TODO Auto-generated method stub
		List<Founder> galleryList = new ArrayList<Founder>();
		try {
			Connection conn = BaseDao.getConn();
			Statement ps = conn.createStatement();
			String sql = "SELECT * FROM Founder";
			System.out.println(sql);
			ResultSet rs = ps.executeQuery(sql);
			while (rs.next()) {
				Founder ngallery = new Founder();
				ngallery.setId(rs.getInt("id"));
				ngallery.setImage(rs.getString("image"));
				ngallery.setName(rs.getString("name"));
				ngallery.setType(rs.getString("fType"));
				galleryList.add(ngallery);
			}
			BaseDao.closeAll(conn, ps, rs);
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		return galleryList;
	}

	@Override
	public List<Reason> Reasons() {
		// TODO Auto-generated method stub
		List<Reason> userList = new ArrayList<Reason>();
		try {
			Connection conn = BaseDao.getConn();
			Statement ps = conn.createStatement();
			String sql = "SELECT * FROM donations ORDER BY id DESC LIMIT 0, 3";
			System.out.println(sql);
			ResultSet rs = ps.executeQuery(sql);
			while (rs.next()) {
				Reason user = new Reason();
				user.setId(rs.getInt("id"));
				user.setAuthor(rs.getString("author"));
				user.setCollect(rs.getInt("collect"));
				user.setImg(rs.getString("img"));
				user.setTargets(rs.getInt("targets"));
				user.setTheme(rs.getString("theme"));
				user.setTitle(rs.getString("title"));
				userList.add(user);
			}
			BaseDao.closeAll(conn, ps, rs);
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		return userList;
	}

	@Override
	public int Updeta(Tu tu) {
		// TODO Auto-generated method stub
		String sql = "UPDATE gallery SET img=? WHERE id=?";
		System.out.println(sql);
		Object[] obj= {tu.getImg(),tu.getId()};
		int num=BaseDao.executeUpdate(sql,obj);
		return num;
	}

	@Override
	public int Insert(Tu tu) {
		// TODO Auto-generated method stub
		String sql = "INSERT INTO gallery(img) VALUES('"+tu.getImg()+"')";
		int num =BaseDao.executeUpdate(sql);
		System.out.println(sql);
		return num;
	}

	@Override
	public List<Tu> allfor() {
		// TODO Auto-generated method stub
		List<Tu> galleryList = new ArrayList<Tu>();
		try {
			Connection conn = BaseDao.getConn();
			Statement ps = conn.createStatement();
			String sql = "SELECT * FROM gallery";
			System.out.println(sql);
			ResultSet rs = ps.executeQuery(sql);
			while (rs.next()) {
				Tu ngallery = new Tu();
				ngallery.setId(rs.getInt("id"));
				ngallery.setImg(rs.getString("img"));
				galleryList.add(ngallery);
			}
			BaseDao.closeAll(conn, ps, rs);
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		return galleryList;
	}

}